﻿using CommunityToolkit.Mvvm.ComponentModel;
using CommunityToolkit.Mvvm.Input;
using DocumentFormat.OpenXml.Spreadsheet;
using SPI_TOOLS.Models;
using SPI_TOOLS.Services;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SPI_TOOLS.ViewModels
{
    public partial class DBManagerViewModel : ObservableObject
    {
        private readonly DatabaseService _databaseService = new();

        [ObservableProperty]
        private DatabaseConnection _connection = new();

        [ObservableProperty]
        private ObservableCollection<string> _databases = new();

        [ObservableProperty]
        private ObservableCollection<TableInfo> _tables = new();

        [ObservableProperty]
        private ObservableCollection<ColumnInfo> _columns = new();

        [ObservableProperty]
        private TableData? _tableData;

        [ObservableProperty]
        private string _statusMessage = "准备就绪";

        [ObservableProperty]
        private bool _isConnected = false;

        [ObservableProperty]
        private TableInfo? _selectedTable;

        [ObservableProperty]
        private string _customQuery = "SELECT * FROM ";

        [ObservableProperty]
        private DataTable? _queryResult;

        [ObservableProperty]
        private string _whereClause = string.Empty;

        [ObservableProperty]
        private bool _isLoading = false;

        [CommunityToolkit.Mvvm.Input.RelayCommand]
        private async Task TestConnectionAsync()
        {
            try
            {
                IsLoading = true;
                StatusMessage = "测试连接中...";

                await _databaseService.TestConnectionAsync(Connection);
                StatusMessage = "连接测试成功!";
            }
            catch (Exception ex)
            {
                StatusMessage = ex.Message;
            }
            finally
            {
                IsLoading = false;
            }
        }

        [RelayCommand]
        private async Task ConnectAsync()
        {
            try
            {
                IsLoading = true;
                StatusMessage = "连接数据库中...";

                // 获取数据库列表
                var dbList = await _databaseService.GetDatabasesAsync(Connection);
                Databases.Clear();
                foreach (var db in dbList)
                {
                    Databases.Add(db);
                }

                IsConnected = true;
                StatusMessage = "连接成功!";
            }
            catch (Exception ex)
            {
                StatusMessage = ex.Message;
                IsConnected = false;
            }
            finally
            {
                IsLoading = false;
            }
        }

        [RelayCommand]
        private async Task LoadTablesAsync()
        {
            if (string.IsNullOrEmpty(Connection.Database))
            {
                StatusMessage = "请先选择数据库";
                return;
            }

            try
            {
                IsLoading = true;
                StatusMessage = "加载表中...";

                var tableList = await _databaseService.GetTablesAsync(Connection);
                Tables.Clear();
                foreach (var table in tableList)
                {
                    Tables.Add(table);
                }

                StatusMessage = $"已加载 {tableList.Count} 个表";
            }
            catch (Exception ex)
            {
                StatusMessage = ex.Message;
            }
            finally
            {
                IsLoading = false;
            }
        }
        [RelayCommand]
        private async Task LoadTableDataAsync()
        {
            if (SelectedTable == null) return;
            if (string.IsNullOrEmpty(SelectedTable.TableName))
            {
                StatusMessage = "请先选择表";
                return;
            }

            try
            {
                IsLoading = true;
                StatusMessage = $"加载表 {SelectedTable.TableName} 数据中...";

                // 加载表结构
                var columnList = await _databaseService.GetTableColumnsAsync(Connection, SelectedTable.TableName);
                Columns.Clear();
                foreach (var column in columnList)
                {
                    Columns.Add(column);
                }

                // 加载表数据
                //TableData = await _databaseService.GetTableDataAsync(Connection, SelectedTable.TableName, WhereClause);

                //StatusMessage = $"表 {SelectedTable} 加载完成，共 {TableData.Rows.Count} 行";
            }
            catch (Exception ex)
            {
                StatusMessage = ex.Message;
            }
            finally
            {
                IsLoading = false;
            }
        }

        [RelayCommand]
        private async Task ExecuteCustomQueryAsync()
        {
            if (string.IsNullOrEmpty(CustomQuery))
            {
                StatusMessage = "请输入查询语句";
                return;
            }

            try
            {
                IsLoading = true;
                StatusMessage = "执行查询中...";

                QueryResult = await _databaseService.ExecuteReaderAsync(Connection, CustomQuery);
                StatusMessage = $"查询执行成功，返回 {QueryResult.Rows.Count} 行";
            }
            catch (Exception ex)
            {
                StatusMessage = ex.Message;
            }
            finally
            {
                IsLoading = false;
            }
        }

        [RelayCommand]
        private async Task AddColumnAsync(string columnDefinition)
        {
            if (SelectedTable == null) return;
            if (string.IsNullOrEmpty(SelectedTable.TableName))
            {
                StatusMessage = "请先选择表";
                return;
            }

            try
            {
                // 这里简化处理，实际应该解析columnDefinition
                // 格式: 列名 数据类型 [NULL|NOT NULL] [DEFAULT 值] [COMMENT '注释']
                var parts = columnDefinition.Split(' ', StringSplitOptions.RemoveEmptyEntries);
                if (parts.Length < 2)
                {
                    StatusMessage = "列定义格式错误";
                    return;
                }

                var columnName = parts[0];
                var dataType = parts[1];
                var isNullable = !columnDefinition.Contains("NOT NULL", StringComparison.OrdinalIgnoreCase);

                await _databaseService.AddColumnAsync(Connection, SelectedTable.TableName, columnName, dataType, isNullable);
                StatusMessage = $"列 {columnName} 添加成功";

                // 刷新表结构
                await LoadTableDataAsync();
            }
            catch (Exception ex)
            {
                StatusMessage = ex.Message;
            }
        }

        [RelayCommand]
        private async Task DropColumnAsync(string columnName)
        {
            if (SelectedTable == null) return;
            if (string.IsNullOrEmpty(SelectedTable.TableName) || string.IsNullOrEmpty(columnName))
            {
                StatusMessage = "请先选择表和列";
                return;
            }

            try
            {
                await _databaseService.DropColumnAsync(Connection, SelectedTable.TableName, columnName);
                StatusMessage = $"列 {columnName} 删除成功";

                // 刷新表结构
                await LoadTableDataAsync();
            }
            catch (Exception ex)
            {
                StatusMessage = ex.Message;
            }
        }

        [RelayCommand]
        private async Task DeleteSelectedRecordsAsync()
        {
            if (SelectedTable == null) return;
            if (string.IsNullOrEmpty(SelectedTable.TableName) || string.IsNullOrEmpty(WhereClause))
            {
                StatusMessage = "请先选择表并输入WHERE条件";
                return;
            }

            try
            {
                var result = await _databaseService.DeleteRecordsAsync(Connection, SelectedTable.TableName, WhereClause);
                if (result)
                {
                    StatusMessage = "记录删除成功";
                    await LoadTableDataAsync(); // 刷新数据
                }
                else
                {
                    StatusMessage = "没有记录被删除";
                }
            }
            catch (Exception ex)
            {
                StatusMessage = ex.Message;
            }
        }

        partial void OnConnectionChanged(DatabaseConnection value)
        {
            // 当连接信息改变时，重置状态
            IsConnected = false;
            Tables.Clear();
            Columns.Clear();
            TableData = null;
        }

        //partial void OnSelectedTableChanged(string value)
        //{
        //    if (!string.IsNullOrEmpty(value))
        //    {
        //        // 自动更新自定义查询
        //        CustomQuery = $"SELECT * FROM `{value}`";
        //    }
        //}
    }

}
